package com.bj58.heatmap.db;

import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class CommonDao {
	/**通用查询类 - 查询指定字段
	 * @throws Exception
	 */
	public  <T> List<T>  getCommonStatList(String sql,List<String> cols,Class<T> clazz,int type) throws Exception{
		System.out.println(sql);
		Connection con = DBConnectionPool.getConnection(type);
		if(null == con){
			for(int i = 0 ; i < 2 ; i++){
				System.out.println("try again "+i);
				con = DBConnectionPool.getConnection(type);
				if(null != con){
					break;
				}
			}
		}
		Statement st = null;
		ResultSet rs = null;
		try {
			System.out.println("common  con  is  " + con);
			st = con.createStatement();
			rs = st.executeQuery(sql);
			return   populateData(rs,clazz, cols);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JdbcUitl.closeResultSet(rs);
			JdbcUitl.closeStatement(st);
			JdbcUitl.closeConnection(con);
		}
		return null;
	}
	
	
	/**通用查询 - 查询所有字段
	 */
	public  <T> List<T>  getAllColsByVo(String sql,Class<T> clazz,int type) throws Exception{
		
//		System.out.println(sql);
		Connection con = DBConnectionPool.getConnection(type);
		if(null == con){
			for(int i = 0 ; i < 2 ; i++){
				System.out.println("try again "+i);
				con = DBConnectionPool.getConnection(type);
				if(null != con){
					break;
				}
			}
		}
		Statement st = null;
		ResultSet rs = null;
		try {
			st = con.createStatement();
			rs = st.executeQuery(sql);
			List<String> cols = new ArrayList<String>();
			Field[]   fs = clazz.getDeclaredFields();
			for(Field f : fs){
				cols.add(f.getName());
			}
			return   populateData(rs,clazz, cols);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JdbcUitl.closeResultSet(rs);
			JdbcUitl.closeStatement(st);
			JdbcUitl.closeConnection(con);
		}
		return null;
	}
	
	private  <T> List<T> populateData(ResultSet resultSet, Class<T> clazz,List<String> cols) throws Exception {
		List<T> dataList = new ArrayList<T>();
		
		Method[] mm = clazz.getMethods();
		Map<String,Method> methodMap = new HashMap<String,Method>();
		for(Method m: mm){
			methodMap.put(m.getName(), m);
		}
		
		Field[] fields = clazz.getDeclaredFields();
		while (resultSet.next()) {
			T bean = clazz.newInstance();
			int count = 1;
			for(String col : cols) {
				Object columnValueObj = null;
				for(Field field : fields){
					if(field.getName().equals(col)){
						Class<?> filedCls = field.getType();
						if(filedCls == int.class || filedCls == Integer.class) {
							columnValueObj = resultSet.getInt(count);
						} else if(filedCls == String.class) {
							columnValueObj = resultSet.getString(count);
						} else if(filedCls == boolean.class || filedCls == Boolean.class) {
							columnValueObj = resultSet.getBoolean(count);
						} else if(filedCls == byte.class || filedCls == Byte.class) {
							columnValueObj = resultSet.getByte(count);
						} else if(filedCls == short.class || filedCls == Short.class) {
							columnValueObj = resultSet.getShort(count);
						} else if(filedCls == long.class || filedCls == Long.class) {
							columnValueObj = resultSet.getLong(count);
						} else if(filedCls == float.class || filedCls == Float.class) {
							columnValueObj = resultSet.getFloat(count);
						} else if(filedCls == double.class || filedCls == Double.class) {
							columnValueObj = resultSet.getDouble(count);
						} else if(filedCls == BigDecimal.class) {
							columnValueObj = resultSet.getBigDecimal(count);
						} 
						else {
							columnValueObj = resultSet.getObject(count);
						}
						
						if (columnValueObj != null) {
							field.setAccessible(true);
							field.set(bean,columnValueObj);
						}
						count ++;
						break;
					}
				}
			}
			dataList.add(bean);
		}
		return dataList;
	}
	
	/**通用查询 - 查询所有字段
	 */
	public  <T> List<T>  getAllColsByVoLabel(String sql,Class<T> clazz,int type) throws Exception{
		
		Connection con = DBConnectionPool.getConnection(type);
		if(null == con){
			for(int i = 0 ; i < 2 ; i++){
				System.out.println("try again "+i);
				con = DBConnectionPool.getConnection(type);
				if(null != con){
					break;
				}
			}
		}
		Statement st = null;
		ResultSet rs = null;
		try {
			st = con.createStatement();
			rs = st.executeQuery(sql);
			List<String> cols = new ArrayList<String>();
			Field[]   fs = clazz.getDeclaredFields();
			for(Field f : fs){
				cols.add(f.getName());
			}
			return   populateData(rs,clazz, cols);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JdbcUitl.closeResultSet(rs);
			JdbcUitl.closeStatement(st);
			JdbcUitl.closeConnection(con);
		}
		return null;
	}
	
	
	/** 计算count值
	 */
	public int countAll(String sql,int type) throws Exception{
		sql = sql.trim();
		Connection con = DBConnectionPool.getConnection(type);
		if(null == con){
			for(int i = 0 ; i < 2 ; i++){
				System.out.println("try again "+i);
				con = DBConnectionPool.getConnection(type);
				if(null != con){
					break;
				}
			}
		}
		Statement st = null;
		ResultSet rs = null;
		try {
			st = con.createStatement();
			rs = st.executeQuery(sql);
			while (rs.next()) {
				int count = rs.getInt(1);
//				MyStore.countTableMap.put(sql, count);
				return count;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JdbcUitl.closeResultSet(rs);
			JdbcUitl.closeStatement(st);
			JdbcUitl.closeConnection(con);
		}
		return 0;
	}
	
	
	
	/**查询返回DOUBLE数值
	 * @param sql
	 * @param type
	 * @return
	 * @throws Exception
	 */
	public   double selectDouble(String sql,int type) throws Exception{
		
		Connection con = DBConnectionPool.getConnection(type);
		Statement st = null;
		ResultSet rs = null;
		DecimalFormat  df = new DecimalFormat("0.00");
		try {
			st = con.createStatement();
			rs = st.executeQuery(sql);
			while (rs.next()) {
				return Double.valueOf(df.format(rs.getDouble(1)));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JdbcUitl.closeResultSet(rs);
			JdbcUitl.closeStatement(st);
			JdbcUitl.closeConnection(con);
		}
		return 0;
	}
	
	/**获取结果 AS  MAP(String:String)
	 */
	public   Map<String,String> selectValueAsMap(String sql,int type) throws Exception{
		
		Connection con = DBConnectionPool.getConnection(type);
		Statement st = null;
		ResultSet rs = null;
		Map<String,String> rsMap = new HashMap<String, String>();
		try {
			st = con.createStatement();
			rs = st.executeQuery(sql);
			while (rs.next()) {
				rsMap.put(String.valueOf(rs.getObject(1)), String.valueOf(rs.getObject(2)));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JdbcUitl.closeResultSet(rs);
			JdbcUitl.closeStatement(st);
			JdbcUitl.closeConnection(con);
		}
		return rsMap;
	}
	
	
	/**LOAD数据
	 * @param filePath  文件路径
	 * @param tableName	表名
	 * @param reg	分隔符
	 * @param type 
	 * @throws SQLException
	 */
	public boolean loadData(String filePath,String tableName,String reg,int type) throws SQLException{
		Connection con = DBConnectionPool.getConnection(type);
		Statement st = null;
		StringBuffer sb = new StringBuffer();
		
		sb.append("load data local infile '"+filePath+"'  into  table "+tableName+"  fields terminated by '"+reg+"'  ");
		try {
			st = con.createStatement();
			st.executeQuery(sb.toString());
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} finally {
			JdbcUitl.closeStatement(st);
			JdbcUitl.closeConnection(con);
		}
		return true;
	}
	
	/**通用查询    将所有数据以字符类型返回，中间以分号隔开
	 * @param Sql
	 * @param colTypes
	 * @param type
	 * @return
	 * @throws Exception
	 */
	public  List<String> searchReturnStrings(String Sql,int type) throws Exception {
		List<String> dataList = new ArrayList<String>();
		
		Connection con = DBConnectionPool.getConnection(type);
		System.out.println(con);
		Statement st = null;
		ResultSet rs = null;
		try {
			st = con.createStatement();
			rs = st.executeQuery(Sql);
			ResultSetMetaData   rsmd = rs.getMetaData();
			int numberOfColumns  = rsmd.getColumnCount();
			while(rs.next()){
				StringBuffer result  = new StringBuffer();
				for(int i = 1 ; i <= numberOfColumns ; i++){
					result.append(String.valueOf(rs.getObject(i)));
					if( i < numberOfColumns){
						result.append(";");
					}
				}
				
				dataList.add(result.toString());
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JdbcUitl.closeResultSet(rs);
			JdbcUitl.closeStatement(st);
			JdbcUitl.closeConnection(con);
		}
		return dataList;
	}
	
	
	
	/**执行Sql
	 * @param type 
	 */
	public  boolean  executeSql(String sql,int type) throws Exception{
		
		Connection con = DBConnectionPool.getConnection(type);
//		System.out.println(sql);
		Statement st = null;
		ResultSet rs = null;
		try {
			st = con.createStatement();
			return st.execute(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JdbcUitl.closeResultSet(rs);
			JdbcUitl.closeStatement(st);
			JdbcUitl.closeConnection(con);
		}
		return false;
	}
	
	/**作为事务，执行一组Sql
	 * @param type 
	 */
	public  boolean  executeSql(List<String> sqlList,int type) throws Exception{
		
		Connection con = DBConnectionPool.getConnection(type);
		Statement st = null;
		ResultSet rs = null;
		try {
			con.setAutoCommit(false);
			st = con.createStatement();
			for(String sql:sqlList){
				st.execute(sql);
			}
			con.commit();
		} catch (Exception e) {
			e.printStackTrace();
			try {
				con.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			return false;
		}finally {
			JdbcUitl.closeResultSet(rs);
			JdbcUitl.closeStatement(st);
			JdbcUitl.closeConnection(con);
		}
		return true;
	}
	
	/**查询ID集合用...
	 */
	public  List<Long> searchReturnLongList(String Sql,int type) throws Exception {
		List<Long> dataList = new ArrayList<Long>();
		
		Connection con = DBConnectionPool.getConnection(type);
		System.out.println(con);
		Statement st = null;
		ResultSet rs = null;
		try {
			st = con.createStatement();
			rs = st.executeQuery(Sql);
			while(rs.next()){
				dataList.add(Long.parseLong(rs.getObject(1).toString()));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JdbcUitl.closeResultSet(rs);
			JdbcUitl.closeStatement(st);
			JdbcUitl.closeConnection(con);
		}
		return dataList;
	}
	
}
